﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

namespace Fotobanka
{
    /// <summary>
    /// Obsluzna trida pro praci se skupinami
    /// </summary>
    public class GroupManager
    {
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

        /// <summary>
        /// Vyhleda uzivatele ve skupine
        /// </summary>
        /// <param name="id">id skupiny</param>
        /// <returns>seznam uzivatelu</returns>
        public List<UserObject> SelectGroupUsers(int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<UserObject> l = new List<UserObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Groups_Users,Users WHERE user_id=Users.id AND group_id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        UserObject o = new UserObject();
                        o.Id = Convert.ToInt32(reader["id"]);
                        o.Email = reader["email"].ToString();
                        o.FirstName = reader["first_name"].ToString();
                        o.LastName = reader["last_name"].ToString();
                        o.LastLogin = (reader["last_login"] != DBNull.Value) ? (DateTime)reader["last_login"] : DateTime.MinValue;
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<UserObject>();
        }

        /// <summary>
        /// Vyhleda vsechny skupiny
        /// </summary>
        /// <returns>seznam skupin</returns>
        public List<GroupObject> SelectAllGroups()
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<GroupObject> l = new List<GroupObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Groups,Users WHERE Groups.user_id=Users.id", connection);

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GroupObject o = new GroupObject();
                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Deleteable = (bool)reader["deleteable"];
                        o.GroupAdmin = reader["email"].ToString();
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<GroupObject>();
        }

        /// <summary>
        /// Vyhleda skupiny do kterych uzivatel patri
        /// </summary>
        /// <param name="user_id">id uzivatele</param>
        /// <returns>seznam skupin</returns>
        public List<GroupObject> SelectUserGroups(int user_id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<GroupObject> l = new List<GroupObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Groups,Users WHERE Groups.user_id = Users.id AND Groups.user_id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", user_id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GroupObject o = new GroupObject();
                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Deleteable = (bool)reader["deleteable"];
                        o.GroupAdmin = reader["email"].ToString();
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<GroupObject>();
        }

        /// <summary>
        /// Vyhleda detail skupiny
        /// </summary>
        /// <param name="id">id skupiny</param>
        /// <returns>skupina</returns>
        public GroupObject SelectGroupDetail(int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    GroupObject o = new GroupObject();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Groups WHERE id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Deleteable = (bool)reader["deleteable"];
                        //o.GroupAdmin = reader["email"].ToString();
                        o.GroupAdminId = Convert.ToInt32(reader["user_id"].ToString());
                    }

                    return o;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new GroupObject();
        }

        /// <summary>
        /// Provede update skupiny v databazi
        /// </summary>
        /// <param name="o">skupina</param>
        public void UpdateGroupDetail(GroupObject o)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("UPDATE Groups SET name=@jmeno, description=@popis, update_on=@updated WHERE id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@jmeno", o.Name));
                    command.Parameters.Add(new SqlParameter("@popis", o.Description));
                    //command.Parameters.Add(new SqlParameter("@userid", id));
                    command.Parameters.Add(new SqlParameter("@updated", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@id", o.Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Vlozi skupinu do databaze
        /// </summary>
        /// <param name="name">nazev</param>
        /// <param name="popis">popis</param>
        /// <param name="id">id uzivatele</param>
        public void InsertToDb(string name, string popis, int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("INSERT INTO Groups (name, description, user_id, create_on, update_on, deleteable) VALUES (@jmeno, @popis, @userid, @created, @updated, @deleteable);", connection);
                    command.Parameters.Add(new SqlParameter("@jmeno", name));
                    command.Parameters.Add(new SqlParameter("@popis", popis));
                    command.Parameters.Add(new SqlParameter("@userid", id));
                    command.Parameters.Add(new SqlParameter("@created", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@updated", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@deleteable", 1));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Prida uzivatele ke skupine
        /// </summary>
        /// <param name="group_id">id skupiny</param>
        /// <param name="user_id">id uzivatele</param>
        public void InsertUserToGroup(int group_id, int user_id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("INSERT INTO Groups_Users  (user_id, group_id) VALUES (@user_id, @group_id);", connection);
                    command.Parameters.Add(new SqlParameter("@user_id", user_id));
                    command.Parameters.Add(new SqlParameter("@group_id", group_id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Smaze skupinu z databaze
        /// </summary>
        /// <param name="Id">id skupiny</param>
        public void DeleteFromDB(int Id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("DELETE FROM Groups WHERE (id = @id);", connection);
                    command.Parameters.Add(new SqlParameter("@id", Id));

                    //pak dodelat i smazani uzivatleu ve skupine(mozna to pujde primo nastavit v db)

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Smaze uzivatelskou skupinu
        /// </summary>
        /// <param name="Id">id skupiny</param>
        public void DeleteUserFromGroup(int Id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("DELETE FROM Groups_Users WHERE (id = @id);", connection);
                    command.Parameters.Add(new SqlParameter("@id", Id));

                    //pak dodelat i smazani uzivatleu ve skupine(mozna to pujde primo nastavit v db)

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Kontrola jestli skupina uz existuje
        /// </summary>
        /// <param name="jmeno">nazev</param>
        /// <param name="id">id uzivatele</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool GroupAlreadyExist(string jmeno, int id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Groups WHERE name=@value AND user_id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@value", jmeno));
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }

        /// <summary>
        /// Kontrola jestli skupina uz existuje
        /// </summary>
        /// <param name="jmeno">nazev</param>
        /// <param name="id">id uzivatele</param>
        /// <param name="group_id">id skupiny</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool GroupAlreadyExist(string jmeno, int id, int group_id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Groups WHERE name=@value AND user_id=@id AND id<>@gid;", connection);
                    command.Parameters.Add(new SqlParameter("@value", jmeno));
                    command.Parameters.Add(new SqlParameter("@id", id));
                    command.Parameters.Add(new SqlParameter("@gid", group_id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }
    
        /// <summary>
        /// Kontrola jestli je uz uzivatel ve skupine
        /// </summary>
        /// <param name="group_id">id skupiny</param>
        /// <param name="user_id">id uzivatele</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool UserExistInGroup(int group_id, int user_id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Groups_Users WHERE group_id=@group_id AND user_id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@group_id", group_id));
                    command.Parameters.Add(new SqlParameter("@id", user_id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }
    
    }
}
